package infocluster.task;

import infocluster.prototype.ExcelProcessTask;
import java.io.IOException;

import jxl.*;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.write.biff.RowsExceededException;

public class CleanupExcelProcessTask extends ExcelProcessTask{

	public boolean execute() throws IOException{
		try {
			Workbook wb = Workbook.getWorkbook(this.getSource(0));
			WritableWorkbook newWb = Workbook.createWorkbook(this.getDest(0));
			
			Sheet sheet = wb.getSheet(0);
			int rowCount = sheet.getRows();
			int columnCount = sheet.getColumns();
			
			WritableSheet newSheet = newWb.createSheet("First Sheet", 0);
			
			int i=0;
			for(;i<2;i++){
				for(int j=0;j<columnCount;j++){
					buildCell(sheet, newSheet, i, j, true); 
				}		
			}
			for(;i<rowCount;i++){
				for(int j=0;j<columnCount;j++){
					buildCell(sheet, newSheet, i, j, false); 
				}
			}
			
			Range[] ranges = sheet.getMergedCells(); 
			for(jxl.Range space:ranges){ 
				int rowFrom = space.getTopLeft().getRow(); 
				int column = space.getTopLeft().getColumn(); 
				int rowTo = space.getBottomRight().getRow();
				String content = space.getTopLeft().getContents().trim();
				WritableCellFormat cf = new WritableCellFormat(space.getTopLeft().getCellFormat());
				cf.setShrinkToFit(true);
				for(i = rowFrom; i <= rowTo; i++){
					Label label = new Label(column, i, content); 
					label.setCellFormat(cf);
					newSheet.addCell(label); 
				}
			}
			
			newWb.write(); 
			newWb.close();
			
			wb.close();
			return true;
		} catch (ArrayIndexOutOfBoundsException e) {
			e.printStackTrace();
			return false;
		} catch (BiffException e) {
			e.printStackTrace();
			return false;
		} catch (RowsExceededException e) {
			e.printStackTrace();
			return false;
		} catch (WriteException e) {
			e.printStackTrace();
			return false;
		}		
	}
	
	/**
	 * @param sheet 
	 * @param newSheet
	 * @param i
	 * @param j
	 * @param setWidth
	 * @throws WriteException
	 * @throws RowsExceededException
	 */
	private void buildCell(Sheet sheet, WritableSheet newSheet, int i, int j, boolean setWidth)
			throws WriteException, RowsExceededException {
		Cell c = sheet.getCell(j,i);
		
		String content = c.getContents().trim();
		Label label = new Label(j, i, content);
		
		WritableCellFormat cf = new WritableCellFormat(c.getCellFormat());
		cf.setShrinkToFit(true);
		label.setCellFormat(cf);
		newSheet.addCell(label);
		
		if(setWidth){
			newSheet.setColumnView(j, sheet.getColumnView(j));
		}
	}
}
